package com.ljm.dao;

import com.ljm.bin.user;
import com.ljm.bin.wxuser;
import com.ljm.util.DBUtil1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author： Administrator
 * @date： 2021-05-14 14:54
 * @version 1.0
 */
public class userDao {
    public static int insert(wxuser us) throws SQLException {

// 1，获取连接.
        Connection con= DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql = "insert into wxuser values(?,?)";
        //2，2创建prepareStatement对象，执行 sqL语句
        PreparedStatement ps = con.prepareStatement(sql);
        //3.给占位符进行赋值
        ps.setString(1,us.getUsername());
        ps.setString(2,us.getPassword());
        int i = ps.executeUpdate();
//3.资源释放
        DBUtil1.close(ps,con);
        return i;
    }
    public static int selectByNameAndPassword(wxuser us) throws ClassNotFoundException, SQLException {

        //1，获取连接.
        int i=0;
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from wxuser where(username=? and password=?)";
        PreparedStatement ps = con.prepareStatement(sql1);
        ps.setString(1,us.getUsername());
        ps.setString(2,us.getPassword());
        ResultSet rs=ps.executeQuery();
        if(rs.next())
            i=1;
        // 4，处理查询的结果集
       /* while(rs.next()){
            // 获取当前行中的数据
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            int state = rs.getInt("state");
            us = new user(id,username,password,state);
        }*/
        // 5，释放资源，还回连接池中
        DBUtil1.close(ps,con);

        return i;
    }
    public static ResultSet selectByUsername(wxuser us) throws ClassNotFoundException, SQLException {

        //1，获取连接.
        int i=0;
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from wxuser where username=?";
        PreparedStatement ps = con.prepareStatement(sql1);
        ps.setString(1,us.getUsername());
        ResultSet rs=ps.executeQuery();
        // 4，处理查询的结果集
        // 5，释放资源，还回连接池中
        DBUtil1.close(ps,con);
        return rs;
    }
    //查询
    public static List<wxuser> result() throws SQLException {
        //0.创建集合
        List<wxuser> user=new ArrayList<>();
        //1，获取连接.
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from wxuser";
        PreparedStatement ps = con.prepareStatement(sql1);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            String username=rs.getString("username");
            String password=rs.getString("password");
            //控制台输出
          /*  System.out.println("id："+id+"，username："+username+
                    "，password："+password+"，state："+state);*/
            wxuser user1=new wxuser(username,password);
            user.add(user1);
        }
        DBUtil1.close(ps,con);
        return user;
    }
    //删除
    public static int delete(wxuser us)throws SQLException{

        //1，获取连接.
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql2= "delete from wxuser where username=? and password=?";
        PreparedStatement ps = con.prepareStatement(sql2);
        ps.setString(1,us.getUsername());
        ps.setString(2,us.getPassword());
        int i = ps.executeUpdate();
        DBUtil1.close(ps,con);
        return i;
    }
    public static int update(wxuser us)throws SQLException{

        //1，获取连接.
        Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql3="update wxuser set password=? where username=?";
        PreparedStatement  ps = con.prepareStatement(sql3);
        ps.setString(1,us.getPassword());
        ps.setString(2,us.getUsername());
        int i = ps.executeUpdate();
        DBUtil1.close(ps,con);
        return i;

    }
//    public static void main(String[] args) throws SQLException, ClassNotFoundException {
////            insert(new wxuser("ljm","20220502"));
//                update(new wxuser("ljm","ljmspace"));
//    }
}

